package com.cfne.cuckoo.law.utils;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 解析excel文档工具类(待扩展),支持xls与xlsx格式
 */
public class POIUtil {
    private static Logger logger = LoggerFactory.getLogger(POIUtil.class);
    private final static String xls = "xls";
    private final static String xlsx = "xlsx";

    /**
     * 解析excel文档(MultipartFile)
     */
    public static List<String[]> readExcel(MultipartFile file) {
        //获取excel文件的io流
        InputStream is = null;
        //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
        List<String[]> list = new ArrayList<String[]>();
        try {
            //检查文件
            checkFile(file);
            //获取excel文件的io流
            is = file.getInputStream();
            //获得Workbook工作薄对象
            Workbook workbook = getWorkBook(file.getOriginalFilename(), is);
            if (workbook != null) {
                for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
                    //获得当前sheet工作表
                    Sheet sheet = workbook.getSheetAt(sheetNum);
                    if (sheet == null) {
                        continue;
                    }
                    //获得当前sheet的开始行
                    int firstRowNum = sheet.getFirstRowNum();
                    //获得当前sheet的结束行
                    int lastRowNum = sheet.getLastRowNum();
                    //获得当前行
                    Row firstRow = sheet.getRow(firstRowNum);
                    if (firstRow == null) {
                        continue;
                    }
                    //获得当前行的开始列
                    int firstCellNum = firstRow.getFirstCellNum();
                    //获得当前行的列数
                    int lastCellNum = firstRow.getPhysicalNumberOfCells();
                    //循环所有行
                    for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
                        //获得当前行
                        Row row = sheet.getRow(rowNum);
                        if (row == null|| (StringUtils.isEmpty(getCellValue(row.getCell(firstCellNum)))&&StringUtils.isEmpty(getCellValue(row.getCell(firstCellNum+3))))&&StringUtils.isEmpty(getCellValue(row.getCell(firstCellNum+4)))) {
                            continue;
                        }
                        String[] cells = new String[lastCellNum];
                        //循环当前行
                        boolean count = false;
                        for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
                            if (!StringUtils.isEmpty(getCellValue(row.getCell(cellNum)))){
                                count=true;
                            }
                            Cell cell = row.getCell(cellNum);
                            cells[cellNum] = getCellValue(cell);
                        }
                        if (count){
                            list.add(cells);
                        }
//                        list.add(cells);
                    }
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    is = null;
                    e.printStackTrace();
                }
            }

        }
        return list;
    }

    /**
     * 解析excel文档(File)
     */
    public static List<String[]> readExcel(File file) {
        //获取excel文件的io流
        InputStream is = null;
        //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
        List<String[]> list = new ArrayList<String[]>();
        try {
            //检查文件
            checkFile(file);
            //获取excel文件的io流
            is = new FileInputStream(file);
            //获得Workbook工作薄对象
            Workbook workbook = getWorkBook(file.getName(), is);
            if (workbook != null) {
                for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
                    //获得当前sheet工作表
                    Sheet sheet = workbook.getSheetAt(sheetNum);
                    if (sheet == null) {
                        continue;
                    }
                    //获得当前sheet的开始行
                    int firstRowNum = sheet.getFirstRowNum();
                    //获得当前sheet的结束行
                    int lastRowNum = sheet.getLastRowNum();
                    //获得当前行
                    Row firstRow = sheet.getRow(firstRowNum);
                    if (firstRow == null) {
                        continue;
                    }
                    //获得当前行的开始列
                    int firstCellNum = firstRow.getFirstCellNum();
                    //获得当前行的列数
                    int lastCellNum = firstRow.getPhysicalNumberOfCells();
                    //循环所有行
                    for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
                        //获得当前行
                        Row row = sheet.getRow(rowNum);
                        if (row == null) {
                            continue;
                        }
                        String[] cells = new String[lastCellNum];
                        //循环当前行
                        for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
                            Cell cell = row.getCell(cellNum);
                            cells[cellNum] = getCellValue(cell);
                        }
                        list.add(cells);
                    }
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    is = null;
                    e.printStackTrace();
                }
            }

        }
        return list;
    }

    /**
     * 检查文件是否合法(MultipartFile)
     */
    public static void checkFile(MultipartFile file) throws IOException {
        //判断文件是否存在
        if (null == file) {
            logger.error("文件不存在！");
            throw new FileNotFoundException("文件不存在！");
        }
        //获得文件名
        String fileName = file.getOriginalFilename();
        //判断文件是否是excel文件
        if (!fileName.endsWith(xls) && !fileName.endsWith(xlsx)) {
            logger.error(fileName + "不是excel文件");
            throw new IOException(fileName + "不是excel文件");
        }
    }

    /**
     * 检查文件是否合法(File)
     */
    public static void checkFile(File file) throws IOException {
        //判断文件是否存在
        if (null == file) {
            logger.error("文件不存在！");
            throw new FileNotFoundException("文件不存在！");
        }
        //获得文件名
        String fileName = file.getName();
        //判断文件是否是excel文件
        if (!fileName.endsWith(xls) && !fileName.endsWith(xlsx)) {
            logger.error(fileName + "不是excel文件");
            throw new IOException(fileName + "不是excel文件");
        }
    }

    /**
     * 根据文件名称,输入流获取excel工作簿对象
     */
    public static Workbook getWorkBook(String fileName, InputStream is) {
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if (fileName.endsWith(xls)) {
                //2003
                workbook = new HSSFWorkbook(is);
            } else if (fileName.endsWith(xlsx)) {
                //2007
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            logger.info(e.getMessage());
        }
        return workbook;
    }

    /**
     * 导出通用
     *
     * @param title
     * @param headers
     * @param list
     * @param out
     */
    public static void exportExcel(String title, String[] headers, List<String[]> list, OutputStream out) {
        //声明一个工作簿
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
        workbook.setCompressTempFiles(true);
        //生成一个表格
        SXSSFSheet sheet = workbook.createSheet(title);
        //设置表格默认列宽度为15个字符
        sheet.setDefaultColumnWidth(20);
        //生成一个样式，用来设置标题样式
        CellStyle style = workbook.createCellStyle();
        //生成一个字体
        Font font = workbook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        //把字体应用到当前的样式
        style.setFont(font);

        //产生表格标题行
        SXSSFRow row = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            SXSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        for (int i = 0; i < list.size(); i++) {
            row = sheet.createRow(i + 1);
            String[] str = list.get(i);
            int j = 0;
            for (int k = 0; k < headers.length; k++) {
                row.createCell(j++).setCellValue(str[k]);
            }
        }
        try {
            workbook.write(out);
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    /**
     * 根据Cell表格对象获取表格中的值
     */
    public static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        //把数字当成String来读，避免出现1读成1.0的情况
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
            } else {
                cell.setCellType(Cell.CELL_TYPE_STRING);
            }
        }
        //判断数据的类型
        switch (cell.getCellType()) {
            //数字
            case Cell.CELL_TYPE_NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    short format = cell.getCellStyle().getDataFormat();
                    SimpleDateFormat sdf = null;
                    if (format == 14 || format == 31 || format == 57 || format == 58
                            || (177 <= format && format <= 178) || (182 <= format && format <= 196)
                            || (210 <= format && format <= 213) || (208 == format)) {
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                    } else if (format == 20 || format == 32 || format == 176 || format == 183 || (200 <= format && format <= 209)) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else if (format == 22) {
                        sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    }
                    if (sdf != null) {
                        Date date =new Date();
                        cellValue = sdf.format(date);
                    } else {
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    }
                } else {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            //字符串
            case Cell.CELL_TYPE_STRING:
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            //Boolean
            case Cell.CELL_TYPE_BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            //公式
            case Cell.CELL_TYPE_FORMULA:
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            //空值
            case Cell.CELL_TYPE_BLANK:
                cellValue = "";
                break;
            //故障
            case Cell.CELL_TYPE_ERROR:
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    /**
     * 判断一行是否为空行
     * poi读取单元格的时候会认为有格式也不算空
     */
    public static boolean isNotEmpty(Row row) {
        if (row == null || row.toString().isEmpty()) {
            return false;
        } else {
            boolean isNotEmpty = false;
            // 从第一个不为空的列开始 到 最后一个 不为空的列(有格式就算一列 )
            for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
                Cell cell = row.getCell(c);
                if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                    isNotEmpty = true;
                    break;
                }
            }
            return isNotEmpty;
        }
    }

    /**
     * 导出设备列表
     *
     * @param title
     * @param headers
     * @param mapList
     * @param out
     */
    public static void exportDevicesExcel(String title, String[] headers, List mapList, OutputStream out) {
        //声明一个工作簿
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
        workbook.setCompressTempFiles(true);
        //生成一个表格
        SXSSFSheet sheet = workbook.createSheet(title);
        //设置表格默认列宽度为15个字符
        sheet.setDefaultColumnWidth(20);
        //生成一个样式，用来设置标题样式
        CellStyle style = workbook.createCellStyle();
        //生成一个字体
        Font font = workbook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        //把字体应用到当前的样式
        style.setFont(font);

        //产生表格标题行
        SXSSFRow row = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            SXSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        for (int i = 0; i < mapList.size(); i++) {
            Map<String, Object> map = (Map<String, Object>) mapList.get(i);
            row = sheet.createRow(i + 1);
            int j = 0;
            row.createCell(j++).setCellValue(map.get("organizationZhName").toString());
            row.createCell(j++).setCellValue(map.get("devId").toString());
            row.createCell(j++).setCellValue(map.get("mac").toString());
            row.createCell(j++).setCellValue(map.get("version").toString());

            switch (map.get("model").toString()) {
                case "1":
                    row.createCell(j++).setCellValue("考勤机");
                    break;
                case "2":
                    row.createCell(j++).setCellValue("门禁机");
                    break;
                case "5":
                    row.createCell(j++).setCellValue("消费机");
                    break;
                case "6":
                    row.createCell(j++).setCellValue("充值");
                    break;
                default:
                    row.createCell(j++).setCellValue("");
                    break;
            }
            row.createCell(j++).setCellValue(map.get("createTime") == null ? "" : map.get("createTime").toString());
        }
        try {
            workbook.write(out);
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }



    /**
     * 导出考勤记录
     *
     * @param title
     * @param headers
     * @param mapList
     * @param out
     */
    public static void exportAttendanceExcel(String title, String[] headers, List mapList, OutputStream out) {
        //声明一个工作簿
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
        workbook.setCompressTempFiles(true);
        //生成一个表格
        SXSSFSheet sheet = workbook.createSheet(title);
        //设置表格默认列宽度为15个字符
        sheet.setDefaultColumnWidth(20);
        //生成一个样式，用来设置标题样式
        CellStyle style = workbook.createCellStyle();
        //生成一个字体
        Font font = workbook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        //把字体应用到当前的样式
        style.setFont(font);

        //产生表格标题行
        SXSSFRow row = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            SXSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        for (int i = 0; i < mapList.size(); i++) {
            Map<String, Object> map = (Map<String, Object>) mapList.get(i);
            row = sheet.createRow(i + 1);
            int j = 0;
            row.createCell(j++).setCellValue(map.get("organizationZhName").toString());
            row.createCell(j++).setCellValue(map.get("userName").toString());
            row.createCell(j++).setCellValue(map.get("userRole").toString().equals("1") ? "学生" : "员工");
            row.createCell(j++).setCellValue(map.get("date") == null ? "" : map.get("date").toString());
            row.createCell(j++).setCellValue(map.get("arrivalTime") == null ? "" : map.get("arrivalTime").toString());
            row.createCell(j++).setCellValue(map.get("departureTime") == null ? "" : map.get("departureTime").toString());
            switch (Objects.isNull(map.get("arrivalStatus")) ? "2" :map.get("arrivalStatus").toString()) {
                case "0":
                    row.createCell(j++).setCellValue("正常");
                    break;
                case "1":
                    row.createCell(j++).setCellValue("迟到");
                    break;
                case "2":
                    row.createCell(j++).setCellValue("缺勤");
                    break;
            }
            switch (Objects.isNull(map.get("arrivalStatus")) ? "2" :map.get("departureStatus").toString()) {
                case "0":
                    row.createCell(j++).setCellValue("正常");
                    break;
                case "1":
                    row.createCell(j++).setCellValue("早退");
                    break;
                case "2":
                    row.createCell(j++).setCellValue("缺勤");
                    break;
            }
            row.createCell(j++).setCellValue(map.get("createTime").toString());
        }
        try {
            workbook.write(out);
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出账户信息
     *
     * @param title
     * @param headers
     * @param mapList
     * @param out
     */
    public static void exportAccountExcel(String title, String[] headers, List mapList, OutputStream out) {
        //声明一个工作簿
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
        workbook.setCompressTempFiles(true);
        //生成一个表格
        SXSSFSheet sheet = workbook.createSheet(title);
        //设置表格默认列宽度为15个字符
        sheet.setDefaultColumnWidth(20);
        //生成一个样式，用来设置标题样式
        CellStyle style = workbook.createCellStyle();
        //生成一个字体
        Font font = workbook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        //把字体应用到当前的样式
        style.setFont(font);

        //产生表格标题行
        SXSSFRow row = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            SXSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        for (int i = 0; i < mapList.size(); i++) {
            Map<String, Object> map = (Map<String, Object>) mapList.get(i);
            row = sheet.createRow(i + 1);
            int j = 0;
            row.createCell(j++).setCellValue(Objects.isNull(map.get("userName")) ?null : map.get("userName").toString());
            row.createCell(j++).setCellValue(Objects.isNull(map.get("allowance")) ?null : map.get("allowance").toString());
            row.createCell(j++).setCellValue(Objects.isNull(map.get("balance")) ?null : map.get("balance").toString());
            row.createCell(j++).setCellValue(Objects.isNull(map.get("points")) ?null : map.get("points").toString());
            row.createCell(j++).setCellValue(Objects.isNull(map.get("deposit")) ?null : map.get("deposit").toString());
            row.createCell(j++).setCellValue(Objects.isNull(map.get("idCard")) ?null : map.get("idCard").toString());
            row.createCell(j++).setCellValue(Objects.isNull(map.get("gender")) ?null : map.get("gender").toString());
            row.createCell(j++).setCellValue(Objects.isNull(map.get("phone")) ?null : map.get("phone").toString());

        }
        try {
            workbook.write(out);
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出用户日报表
     *
     * @param title
     * @param headers
     * @param mapList
     * @param out
     */
    public static void exportAccountDailyRecordsExcel(String title, String[] headers, List mapList, OutputStream out) {
        //声明一个工作簿
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
        workbook.setCompressTempFiles(true);
        //生成一个表格
        SXSSFSheet sheet = workbook.createSheet(title);
        //设置表格默认列宽度为15个字符
        sheet.setDefaultColumnWidth(20);
        //生成一个样式，用来设置标题样式
        CellStyle style = workbook.createCellStyle();
        //生成一个字体
        Font font = workbook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        //把字体应用到当前的样式
        style.setFont(font);

        //产生表格标题行
        SXSSFRow row = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            SXSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        for (int i = 0; i < mapList.size(); i++) {
            Map<String, Object> map = (Map<String, Object>) mapList.get(i);
            row = sheet.createRow(i + 1);
            int j = 0;
            row.createCell(j++).setCellValue(map.get("date").toString());
            row.createCell(j++).setCellValue(map.get("name").toString());
            row.createCell(j++).setCellValue(map.get("phone").toString());
            row.createCell(j++).setCellValue(map.get("cardSn").toString());
            row.createCell(j++).setCellValue(map.get("balance").toString());
            row.createCell(j++).setCellValue(map.get("allowance").toString());
            row.createCell(j++).setCellValue(map.get("useTotal").toString());
            row.createCell(j++).setCellValue(map.get("useBalance").toString());
            row.createCell(j++).setCellValue(map.get("useAllowance").toString());
        }
        try {
            workbook.write(out);
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出组织日报表
     *
     * @param title
     * @param headers
     * @param mapList
     * @param out
     */
    public static void exportDinerDailyRecordsExcel(String title, String[] headers, List mapList, OutputStream out) {
        //声明一个工作簿
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
        workbook.setCompressTempFiles(true);
        //生成一个表格
        SXSSFSheet sheet = workbook.createSheet(title);
        //设置表格默认列宽度为15个字符
        sheet.setDefaultColumnWidth(20);
        //生成一个样式，用来设置标题样式
        CellStyle style = workbook.createCellStyle();
        //生成一个字体
        Font font = workbook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        //把字体应用到当前的样式
        style.setFont(font);

        //产生表格标题行
        SXSSFRow row = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            SXSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        for (int i = 0; i < mapList.size(); i++) {
            Map<String, Object> map = (Map<String, Object>) mapList.get(i);
            row = sheet.createRow(i + 1);
            int j = 0;
            row.createCell(j++).setCellValue(map.get("date").toString());
            row.createCell(j++).setCellValue(map.get("dinerNum").toString());
            row.createCell(j++).setCellValue(map.get("totalCost").toString());
            row.createCell(j++).setCellValue(map.get("averageDinerCost").toString());
            row.createCell(j++).setCellValue(map.get("orderMealNum").toString());
            row.createCell(j++).setCellValue(map.get("orderCost").toString());
            row.createCell(j++).setCellValue(map.get("averageOrderCost").toString());
        }
        try {
            workbook.write(out);
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出组织月报表
     *
     * @param title
     * @param headers
     * @param mapList
     * @param out
     */
    public static void exportDinerMonthRecordsExcel(String title, String[] headers, List mapList, OutputStream out) {
        //声明一个工作簿
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
        workbook.setCompressTempFiles(true);
        //生成一个表格
        SXSSFSheet sheet = workbook.createSheet(title);
        //设置表格默认列宽度为15个字符
        sheet.setDefaultColumnWidth(20);
        //生成一个样式，用来设置标题样式
        CellStyle style = workbook.createCellStyle();
        //生成一个字体
        Font font = workbook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        //把字体应用到当前的样式
        style.setFont(font);

        //产生表格标题行
        SXSSFRow row = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            SXSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        for (int i = 0; i < mapList.size(); i++) {
            Map<String, Object> map = (Map<String, Object>) mapList.get(i);
            row = sheet.createRow(i + 1);
            int j = 0;
            row.createCell(j++).setCellValue(Objects.isNull(map.get("month")) ?null : map.get("month").toString());
            row.createCell(j++).setCellValue(Objects.isNull(map.get("dinerNum")) ?null :map.get("dinerNum").toString());
            row.createCell(j++).setCellValue(Objects.isNull(map.get("totalCost")) ?null :map.get("totalCost").toString());
            row.createCell(j++).setCellValue(Objects.isNull(map.get("averageCost")) ?null :map.get("averageCost").toString());
            row.createCell(j++).setCellValue(Objects.isNull(map.get("orderMealNum")) ?null : map.get("orderMealNum").toString());
            row.createCell(j++).setCellValue(Objects.isNull(map.get("orderMealCost")) ?null :map.get("orderMealCost").toString());
            row.createCell(j++).setCellValue(Objects.isNull(map.get("averageOrderCost")) ?null :map.get("averageOrderCost").toString());
            row.createCell(j++).setCellValue(Objects.isNull(map.get("averageDailyDiner")) ?null :map.get("averageDailyDiner").toString());
            row.createCell(j++).setCellValue(Objects.isNull(map.get("averageDailyCost")) ?null :map.get("averageDailyCost").toString());
            row.createCell(j++).setCellValue(Objects.isNull(map.get("averageBreakfastDiner")) ?null :map.get("averageBreakfastDiner").toString());
            row.createCell(j++).setCellValue(Objects.isNull(map.get("averageLunchDiner")) ?null :map.get("averageLunchDiner").toString());
            row.createCell(j++).setCellValue(Objects.isNull(map.get("averageDinnerDiner")) ?null :map.get("averageDinnerDiner").toString());
        }
        try {
            workbook.write(out);
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出用户就餐时段报表
     *
     * @param title
     * @param headers
     * @param mapList
     * @param out
     */
    public static void exportDinerPeriodRecordsExcel(String title, String[] headers, List mapList, OutputStream out) {
        //声明一个工作簿
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
        workbook.setCompressTempFiles(true);
        //生成一个表格
        SXSSFSheet sheet = workbook.createSheet(title);
        //设置表格默认列宽度为15个字符
        sheet.setDefaultColumnWidth(20);
        //生成一个样式，用来设置标题样式
        CellStyle style = workbook.createCellStyle();
        //生成一个字体
        Font font = workbook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        //把字体应用到当前的样式
        style.setFont(font);

        //产生表格标题行
        SXSSFRow row = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            SXSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        for (int i = 0; i < mapList.size(); i++) {
            Map<String, Object> map = (Map<String, Object>) mapList.get(i);
            row = sheet.createRow(i + 1);
            int j = 0;
            row.createCell(j++).setCellValue(map.get("date").toString());
            switch (map.get("mealType").toString()) {
                case "breakfast":
                    row.createCell(j++).setCellValue("早餐");
                    break;
                case "lunch":
                    row.createCell(j++).setCellValue("午餐");
                    break;
                case "dinner":
                    row.createCell(j++).setCellValue("晚餐");
                    break;
            }
            row.createCell(j++).setCellValue(map.get("dinerNum").toString());
            row.createCell(j++).setCellValue(map.get("totalCost").toString());
            row.createCell(j++).setCellValue(map.get("averageCost").toString());
            row.createCell(j++).setCellValue(map.get("orderMealNum").toString());
            row.createCell(j++).setCellValue(map.get("orderMealCost").toString());
            row.createCell(j++).setCellValue(map.get("averageOrderCost").toString());
        }
        try {
            workbook.write(out);
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void exportBankRecordsExcel(String title, String[] headers, List mapList, OutputStream out,String type) {
        //声明一个工作簿
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
        workbook.setCompressTempFiles(true);
        //生成一个表格
        SXSSFSheet sheet = workbook.createSheet(title);
        //设置表格默认列宽度为15个字符
        sheet.setDefaultColumnWidth(20);
        //生成一个样式，用来设置标题样式
        CellStyle style = workbook.createCellStyle();
        //生成一个字体
        Font font = workbook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        //把字体应用到当前的样式
        style.setFont(font);

        //产生表格标题行
        SXSSFRow row = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            SXSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        if (!CollectionUtils.isEmpty(mapList)) {
            for (int i = 0; i < mapList.size(); i++) {
                Map<String, Object> map = (Map<String, Object>) mapList.get(i);
                row = sheet.createRow(i + 1);
                int j = 0;
                if(Objects.equals(type,"year")){
                    row.createCell(j++).setCellValue(map.get("year").toString());
                }else {
                    row.createCell(j++).setCellValue(map.get("month").toString());
                }
                row.createCell(j++).setCellValue(map.get("visits").toString());
                row.createCell(j++).setCellValue(map.get("totalStudents").toString());
                row.createCell(j++).setCellValue(map.get("incrementStudents").toString());
                row.createCell(j++).setCellValue(map.get("totalSchools").toString());
                row.createCell(j++).setCellValue(map.get("incrementSchools").toString());
            }
        }
        try {
            workbook.write(out);
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static void exportOrgRecordsExcel (String title, String[] headers, List mapList, OutputStream out,String type) throws IOException {
        //声明一个工作簿
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
        workbook.setCompressTempFiles(true);
        //生成一个表格
        SXSSFSheet sheet = workbook.createSheet(title);
        //设置表格默认列宽度为15个字符
        sheet.setDefaultColumnWidth(20);
        //生成一个样式，用来设置标题样式
        CellStyle style = workbook.createCellStyle();
        //生成一个字体
        Font font = workbook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        //把字体应用到当前的样式
        style.setFont(font);

        //产生表格标题行
        SXSSFRow row = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            SXSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        if (!CollectionUtils.isEmpty(mapList)) {
            for (int i = 0; i < mapList.size(); i++) {
                Map<String, Object> map = (Map<String, Object>) mapList.get(i);
                row = sheet.createRow(i + 1);
                int j = 0;
                if(Objects.equals(type,"year")){
                    row.createCell(j++).setCellValue(map.get("year").toString());
                }else {
                    row.createCell(j++).setCellValue(map.get("month").toString());
                }
                row.createCell(j++).setCellValue(map.get("visits").toString());
                row.createCell(j++).setCellValue(map.get("totalStudents").toString());
                row.createCell(j++).setCellValue(map.get("incrementStudents").toString());
            }
        }
        try {
            workbook.write(out);
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    /**
     * 导出员工模板
     * @param title
     * @param headers
     * @param out
     */
    public static void exportUsersRecordsExcel(String title, String[] headers, OutputStream out) {
        //声明一个工作簿
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
        workbook.setCompressTempFiles(true);
        //生成一个表格
        SXSSFSheet sheet = workbook.createSheet(title);
        //设置表格默认列宽度为15个字符
        sheet.setDefaultColumnWidth(20);
        //生成一个样式，用来设置标题样式
        CellStyle style = workbook.createCellStyle();
        //生成一个字体
        Font font = workbook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        //把字体应用到当前的样式
        style.setFont(font);

        //产生表格标题行
        SXSSFRow row = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            SXSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        try {
            workbook.write(out);
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}
